/**
 * 课程列表模块数据操作文件
 */

const { query, queryFirst, format, queryPage } = require('./common');

/**
 * 根据教师ID查询第一条数据
 * @param {*} id 
 * @returns 
 */
const getFirstCourseByTeacherId = (id) => queryFirst('SELECT * FROM course_list WHERE teacher_id = 7 AND end_date >= CURRENT_TIMESTAMP() LIMIT 1', [id]);

/**
 * 添加课程
 * @param {*} obj 
 * @returns 
 */
const addCourse = obj => query(`INSERT INTO course_list SET ?`, obj);

/**
 * 修改课程
 * @param {*} id 
 * @param {*} obj 
 * @returns 
 */
const updateCourse = (id, obj) => query(`UPDATE course_list SET ? WHERE id = ?`, [obj, id]);

/**
 * 删除课程
 * @param {*} id 
 * @returns 
 */
const deleteCourse = id => query(`DELETE FROM course_list WHERE id = ?`, [id]);

/**
 * 根据ID获取数据
 * @param {*} id 
 * @returns 
 */
const getById = id => queryFirst('SELECT * FROM course_list WHERE id = ?', [id]);

/**
 * 搜索课表
 * @param {*} obj 
 */
const searchCourseList = obj => {
    const { class_id, subject_id, teacher_id, from_date, end_date } = obj;
    const where = format(`WHERE (IFNULL(?,'') = '' OR c.from_date >= ?) AND (IFNULL(?,'') = '' OR c.from_date <= ?) AND (IFNULL(?,0) = 0 OR c.class_id = ?) AND (IFNULL(?,0) = 0 OR c.subject_id = ?) AND (IFNULL(?,0) = 0 OR c.teacher_id = ?)`, [
        from_date, from_date, end_date, end_date, class_id, class_id, subject_id, subject_id, teacher_id, teacher_id
    ]);
    return queryPage(obj, `SELECT * FROM(
        SELECT c.*,d.name subject_name,t.name teacher_name,cl.name class_name,ROW_NUMBER() OVER(ORDER BY from_date DESC)num FROM course_list c
        INNER JOIN class_list cl ON c.class_id = cl.id
        INNER JOIN dictionary d ON c.subject_id = d.id
        INNER JOIN teachers t ON c.teacher_id = t.id
        ${where})t
        WHERE t.num > ? AND t.num <= ?`,
        `SELECT COUNT(1) count FROM course_list c ${where}`);
}

/**
 * 为了选择器的搜索课表
 * @param {*} obj 
 */
const searchCourseListForSelector = obj => {
    const { class_id, subject_id, teacher_id, from_date, end_date, self_id } = obj;
    const where = format(`WHERE (IFNULL(?,'') = '' OR c.from_date >= ?) AND (IFNULL(?,'') = '' OR c.from_date <= ?) AND (IFNULL(?,0) = 0 OR c.class_id = ?) AND (IFNULL(?,0) = 0 OR c.subject_id = ?) AND (IFNULL(?,0) = 0 OR c.teacher_id <> ?) AND (IFNULL(?,0) = 0 OR c.teacher_id = ?)`, [
        from_date, from_date, end_date, end_date, class_id, class_id, subject_id, subject_id, self_id, self_id, teacher_id, teacher_id
    ]);
    return queryPage(obj, `SELECT * FROM(
        SELECT c.*,d.name subject_name,t.name teacher_name,cl.name class_name,ROW_NUMBER() OVER(ORDER BY from_date DESC)num FROM course_list c
        INNER JOIN class_list cl ON c.class_id = cl.id
        INNER JOIN dictionary d ON c.subject_id = d.id
        INNER JOIN teachers t ON c.teacher_id = t.id
        ${where})t
        WHERE t.num > ? AND t.num <= ?`,
        `SELECT COUNT(1) count FROM course_list c ${where}`);
}

/**
 * 获取交集数据
 * @param {*} obj 
 */
const getIntersection = ({ teacher_id, class_id, from_date, end_date, id = 0 }) => queryFirst(`SELECT * FROM course_list WHERE id <> ? AND (class_id = ? OR teacher_id = ?) AND ((from_date >= ? AND from_date <= ?) OR (end_date >= ? AND end_date <= ?) OR (? >= from_date AND ? <= end_date)) LIMIT 1`,
    [id, class_id, teacher_id, from_date, end_date, from_date, end_date, from_date, end_date]);

/**
 * 根据多个ID获取多个课程
 * @param {*} ids 
 * @returns 
 */
const getCourseByIds = ids => query(`SELECT id,teacher_id,from_date,end_date FROM course_list WHERE id IN(${ids.join()})`);

module.exports = {
    getById,
    addCourse,
    updateCourse,
    deleteCourse,
    getCourseByIds,
    getIntersection,
    searchCourseList,
    getFirstCourseByTeacherId,
    searchCourseListForSelector
}